var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery, 
   usequery_pre} = require('./server_util.js')

const log = require('../../../logger.js');
const Redisdb = require('../../../db/Redisdb.js');
var logger = require('../../../logger.js').logger;

//查询任务中待办业务
router.post('/query_db_task', async function (req, res) {
   let { storage } = req.body
   //管理待办
   var zcbData = await usequery(`select '注册申请' rwlx,* from shipSRM_zcb where clzt='未处理' order by create_time desc`)
   var gysbgData = await usequery(`select '供应商变更' rwlx,* from shipSRM_gys_bgjl where clzt='未审批' order by create_time desc`)
   var pbdata = await usequery(`select '评标' rwlx,* from shipSRM_xm where xmzt='评标' and 
      getdate() between pbkssj and pbjssj and pbfzrid='${storage.userid}'`)
   var lxdata = await usequery(`select '遴选' rwlx,* from shipSRM_xm where xmzt='遴选'  and pbfzrid='${storage.userid}'`)
   var jjzcdata = await usequery(`select '竞价主持' rwlx,* from shipSRM_xm where (xmzt='竞价中' or xmzt='待竞价')`)
   var zbfpdata = await usequery(`
      SELECT '中标分配' rwlx,main.*,ISNULL(zbmxCount.SubTableCount, 0) AS SubTableCount 
      FROM shipSRM_xm as main 
      OUTER APPLY (
         SELECT COUNT(*) AS SubTableCount 
         FROM shipSRM_xm_zbmx as sub 
         WHERE sub.mainid = main.id
      ) AS zbmxCount
      OUTER APPLY (
         SELECT COUNT(*) AS ValidBidCount 
         FROM shipSRM_gys_bjmx as bj
         INNER JOIN shipSRM_xm_gysmx as gysmx ON bj.xmid = gysmx.mainid AND bj.gysid = gysmx.gysid
         WHERE bj.xmid = main.id 
           AND COALESCE(gysmx.sftt,'否') != '是'
           AND NOT EXISTS (
              SELECT 1 FROM shipSRM_xm_ttmx tt 
              WHERE tt.xmid = bj.xmid 
                AND tt.gysid = bj.gysid 
                AND tt.bjwlmxid = bj.bjwlmxid
           )
      ) AS validBidCount
      WHERE main.xmzt='竞价结束' 
        AND ISNULL(zbmxCount.SubTableCount, 0) = 0
        AND ISNULL(validBidCount.ValidBidCount, 0) > 0
   `)
   //租户待办
   var gysybData = await usequery(`select '应标' rwlx,a.ybzt,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id  and b.xmzt= '待应标' and shzt = '审核通过'
      and a.ybzt is null and a.gysmc='${storage.ygxm}'`)
   var gysdjbData = await usequery(`select '待竞标' rwlx,a.ybzt,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id  and b.xmzt= '待竞价' 
      and a.gysmc='${storage.ygxm}' and (a.sftt!='是' or a.sftt is null) and ybzt is not null`)
   var gysjbzData = await usequery(`select '竞标中' rwlx,a.ybzt,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id  and b.xmzt= '竞价中'
         and a.gysmc='${storage.ygxm}' and (a.sftt!='是' or a.sftt is null) and ybzt is not null`)
   var gystbdata =await usequery(`select '投标' rwlx,a.ybzt,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id   and b.zbxs='投标'  and shzt = '审核通过' and xmzt = '投标'
      and a.gysmc='${storage.ygxm}' and a.ybzt is null`)
   var gyszbdata =await usequery(`select '中标确认' rwlx,b.*,c.zbzt from (select DISTINCT gysmc,mainid from shipSRM_xm_zbmx) a,shipSRM_xm b,shipSRM_xm_gysmx c where a.mainid=b.id 
      and  b.xmzt='竞价结束' and a.gysmc='${storage.ygxm}' and c.mainid=b.id 
      and c.gysmc='${storage.ygxm}' and c.zbzt is null`)
   var gysxjdata =await usequery(`select '询价' rwlx,a.ybzt,b.* from shipSRM_xm_gysmx a,shipSRM_xm b where a.mainid=b.id   and b.xmzt='询价中' 
         and a.gysmc='${storage.ygxm}' and a.ybzt is null`)
   res_util(res, { zcbData, gysbgData, gysybData, gysdjbData,gysjbzData,gystbdata,gyszbdata,gysxjdata,pbdata,lxdata,jjzcdata,zbfpdata}, true, "查询成功")
})


//查询菜单
router.post('/queryMobileMenuList', async function (req, res) {
   var querytensql = `select b.* from
   (select a.jsbh,b.cdmc,b.cdqx,b.ssmk,b.sfsc
   from shipSRM_js a,shipSRM_js_mx  b,shipSRM_ryda c 
    where a.id=b.mainid and c.jsid=a.id and c.id='${req.body.storage.userid}' and ssmk='PDA') a,
    (select * from  shipSRM_bgmenu   where ssmk='PDA') b
    where a.cdqx=b.cdbh order by cdpx asc`
   var rtdata = await usequery(querytensql);
   res_util(res, rtdata, true, "查询成功")
});






//popview 
router.post('/getpopupview', async function (req, res) {
   var db = new Msdb();
   var viewname = req.body.viewname;
   var checkifviewres = await db.query(`SELECT * FROM sys.views WHERE name = '${viewname}' `)
   if (checkifviewres.recordsets[0].length == 0) {
      res_util(res, [], false, "不是视图查询失败")
   }
   else {
      var rtdata = await db.query(`select  TOP 1000 *  from ${viewname}`)
      res_util(res, rtdata.recordsets[0], true, "查询成功！")
   }

});

//查询已发布的通知公告
router.post('/query_fb_tzgg', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_tzgg where fbzt='已发布' order by create_time desc`)
   res_util(res, {rtdata}, true, "查询成功")
})

//发布通知公告
router.post('/fb_tzgg', async function (req, res) {
   let { data,storage } = req.body
   var sql = `update shipSRM_tzgg set fbzt='已发布',fbsj=getdate(),fbr='${storage.ygxm}' where id='${data.id}'`
   var rtdata = await usequery(sql)
   await saveczlog_util(sql, storage, '', '发布通知公告', '发布', 'shipSRM_tzgg')
   res_util(res, {rtdata}, true, "发布成功")
})

//撤销发布通知公告
router.post('/cxfb_tzgg', async function (req, res) {
   let { data,storage } = req.body
   var sql = `update shipSRM_tzgg set fbzt='待发布',fbsj=null,fbr=null where id='${data.id}'`
   var rtdata = await usequery(sql)
   await saveczlog_util(sql, storage, '', '撤销发布通知公告', '撤销发布', 'shipSRM_tzgg')
   res_util(res, {rtdata}, true, "撤销发布成功")
})

//查询通知公告·列表
router.post('/tzgglb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_tzgg order by create_time desc`)
   res_util(res, {rtdata}, true, "查询成功")
})

//查询通知公告·列表通过id
router.post('/query_tzgg_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_tzgg where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0]}, true, "查询成功")
})

//新增通知公告·
router.post('/add_tzgg', async function (req, res) {
   let { formdata, storage} = req.body
   var insrtobj = await insert_table(formdata, "shipSRM_tzgg")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增通知公告·', '新增', 'shipSRM_tzgg')
   res_util(res, { formdata: insrtobj.data}, true, "新增成功")
})

//更新通知公告·
router.post('/update_tzgg', async function (req, res) {
   let { formdata, storage,mxdata,delidarr} = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_tzgg")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新通知公告·', '更新', 'shipSRM_tzgg')

   res_util(res, { formdata: updatobj.data}, true, "更新成功")
})

//删除通知公告·
router.post('/delete_tzgg', async function (req, res) {
   let { data, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_tzgg where id = '${data.id}'`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除通知公告·', '删除', 'shipSRM_tzgg')
   res_util(res, {}, true, "删除成功")
})


//查询在线表单列表
router.post('/oltable_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_oltable order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询在线表单列表通过id
router.post('/query_oltable_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_oltable where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_oltable_mx where mainid ='${id}'`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})



//新增在线表单
router.post('/add_oltable', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util()
   var insrtobj = await insert_table(formdata, "shipSRM_oltable")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增在线表单', '新增', 'shipSRM_oltable')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_oltable_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增在线表单明细', '新增', 'shipSRM_oltable_mx')
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_oltable_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "新增在线表单成功")
})


//更新在线表单
router.post('/update_oltable', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_oltable")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改在线表单', '修改', 'shipSRM_oltable')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_oltable_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_oltable_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_oltable_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_oltable_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_oltable_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新在线表单成功")
})




//  删除在线表单
router.post('/delete_oltable', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_oltable where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_oltable_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除在线表单', '删除', 'shipSRM_oltable')
   res_util(res, {}, true, "删除成功")
})






//  同步生产数据库
router.post('/oltable_syn', async function (req, res) {
   let { selectedRows, storage } = req.body

   var db = new Msdb()
   var zdlist = await usequery(`select * from shipSRM_oltable_mx where mainid='${selectedRows[0].id}'`)
   var ifcztable = await checkbysql(`select * from sys.tables where name='${selectedRows[0].table_name}'`)
   if (!ifcztable) {
      var zdsqlarr = zdlist.map(item => {
         var rtsql = ""
         if (item.zdlx == 'String') { rtsql = `${item.zdbm} nvarchar(${item.zdcd}) COLLATE Chinese_PRC_CI_AI` }
         else if (item.zdlx == 'Interger') { rtsql = `${item.zdbm} int ` }
         else if (item.zdlx == 'Double') { rtsql = `${item.zdbm} float ` }
         else if (item.zdlx == 'Date') { rtsql = `${item.zdbm} datetime ` }
         else if (item.zdlx == 'Text') { rtsql = `${item.zdbm} ntext COLLATE Chinese_PRC_CI_AI` }
         else;
         return rtsql;
      })
      //走新增逻辑
      var exesql = `create table ${selectedRows[0].table_name} (
      id VARCHAR(36) COLLATE Chinese_PRC_CI_AI  PRIMARY KEY ,
      create_by NVARCHAR(100) COLLATE Chinese_PRC_CI_AI,
      update_by NVARCHAR(100) COLLATE Chinese_PRC_CI_AI,
      create_time datetime ,
      update_time datetime ,
      ${zdsqlarr.join(",")}
  );`
      await db.update(exesql)
      await db.update(`update shipSRM_oltable set sftb='已同步' where id='${selectedRows[0].id}'`)
      res_util(res, {}, true, "新增同步成功")

   }
   else {
      //走修改逻辑
      //数据库已有字段list 
      var nowtablezdarr = await usequery(`
      SELECT COLUMN_NAME zdbm FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '${selectedRows[0].table_name}' and COLUMN_NAME!='id'
and COLUMN_NAME!='create_by' and COLUMN_NAME!='create_time' and COLUMN_NAME!='update_by'
and COLUMN_NAME!='update_time' and COLUMN_NAME!='sys_org_code';`)

      //对比zdlist nowtablezdarr标记新增 修改 


      //console.log("nnnnnnzdaaaaaaaaaaaarrrrrrrrrrrrrrrrr",nowtablezdarr)

      const updatelist = zdlist.filter(item1 => nowtablezdarr.some(item2 => item1.zdbm === item2.zdbm));
      const addlist = zdlist.filter(item1 => !nowtablezdarr.some(item2 => item1.zdbm === item2.zdbm));

      const deletelist = nowtablezdarr.filter(item1 => !zdlist.some(item2 => item1.zdbm === item2.zdbm));

      // console.log(updatelist)
      //console.log('222222222222222',addlist)
      //console.log('33333333333333333',deletelist)
      //修改
      //logger.info("修改字段", updatelist)
      if (updatelist.length > 0) {

         updatelist.forEach(async (item) => {
            var exesql = ""
            if (item.zdlx == 'String') { exesql = `${item.zdbm} nvarchar(${item.zdcd}) COLLATE Chinese_PRC_CI_AI` }
            else if (item.zdlx == 'Interger') { exesql = `${item.zdbm} int ` }
            else if (item.zdlx == 'Double') { exesql = `${item.zdbm} float ` }
            else if (item.zdlx == 'Date') { exesql = `${item.zdbm} datetime` }
            else if (item.zdlx == 'Text') { exesql = `${item.zdbm} ntext COLLATE Chinese_PRC_CI_AI` }
            else;
            logger.info(`ALTER TABLE ${selectedRows[0].table_name} ALTER COLUMN ${exesql} ;`)
            await db.update(`ALTER TABLE ${selectedRows[0].table_name} ALTER COLUMN ${exesql} ;`)

         })
      }
      else;
      //新增
      if (addlist.length > 0) {

         addlist.forEach(async (item) => {
            var exesql = ""
            if (item.zdlx == 'String') { exesql = `${item.zdbm} nvarchar(${item.zdcd}) COLLATE Chinese_PRC_CI_AI` }
            else if (item.zdlx == 'Interger') { exesql = `${item.zdbm} int ` }
            else if (item.zdlx == 'Double') { exesql = `${item.zdbm} float ` }
            else if (item.zdlx == 'Date') { exesql = `${item.zdbm} datetime` }
            else if (item.zdlx == 'Text') { exesql = `${item.zdbm} ntext COLLATE Chinese_PRC_CI_AI` }
            else;
            logger.info(`ALTER TABLE ${selectedRows[0].table_name} ADD  ${exesql} ;`)
            await db.update(`ALTER TABLE ${selectedRows[0].table_name} ADD  ${exesql} ;`)
         })
      }
      else;
      //删除


      if (deletelist.length > 0) {

         deletelist.forEach(async (item) => {

            logger.info(`ALTER TABLE ${selectedRows[0].table_name} DROP COLUMN  ${item.zdbm};`)
            await db.update(`ALTER TABLE ${selectedRows[0].table_name} DROP COLUMN  ${item.zdbm};`)
         })

      }
      else;


      await db.update(`update shipSRM_oltable set sftb='已同步' where id='${selectedRows[0].id}'`)
      await db.update(`update shipSRM_oltable_mx set zdzt='已同步' where mainid='${selectedRows[0].id}'`)

      res_util(res, {}, true, "修改同步成功")

   }

})

//  查询一级目录名称
router.post('/select_bgcd', async function (req, res) {
   var rtdata = await usequery(`select cdmc as value,cdmc as label from shipSRM_bgmenu where jjcd ='1'`)
   res_util(res, rtdata, true, "查询成功")
})


//查询报工菜单列表
router.post('/bgcdlb', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_bgmenu order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询报工菜单列表通过id
router.post('/query_bgcd_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_bgmenu where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增报工菜单
router.post('/add_bgcd', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbh = await checkbysql(` select top 1 * from shipSRM_bgmenu where cdbh='${formdata.cdbh.value}'`)
   if (ifbh) {
      res_util(res, { formdata }, false, "新增失败，菜单编号已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_bgmenu")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增报工菜单', '新增', 'shipSRM_bgmenu')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新报工菜单
router.post('/update_bgcd', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_bgmenu where 
   cdbh='${formdata.cdbh.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，物料编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_bgmenu")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新报工菜单', '更新', 'shipSRM_bgmenu')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除报工菜单
router.post('/delete_bgcd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_bgmenu where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除报工菜单', '删除', 'shipSRM_bgmenu')
   res_util(res, {}, true, "删除成功")
})

//查询业务角色列表
router.post('/js_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_js order by id desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询业务角色列表通过id
router.post('/query_anpz_byjs', async function (req, res) {
   var js = req.body.js
   var formdata = await usequery(`select *  from shipSRM_js where jsmc ='${js}'`)
   res_util(res, { formdata: formdata[0] }, true, "查询成功")
})

//查询业务角色列表通过id
router.post('/query_js_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_js where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_js_mx where mainid ='${id}' order by create_time desc`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})

//新增业务角色
router.post('/add_js', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util()
   var insrtobj = await insert_table(formdata, "shipSRM_js")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增业务角色', '新增', 'shipSRM_js')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_js_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增业务角色明细', '新增', 'shipSRM_js_mx')
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_js_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "新增业务角色成功")
})

//更新业务角色
router.post('/update_js', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_js")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改业务角色', '修改', 'shipSRM_js')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_js_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_js_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_js_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_js_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_js_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新业务角色成功")
})

//  删除业务角色
router.post('/delete_js', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_js where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_js_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除业务角色', '删除', 'shipSRM_js')
   res_util(res, {}, true, "删除成功")
})

//按钮权限分配
router.post('/updateJsAnpz', async function (req, res) {
   let { anpz,id, storage } = req.body
   let db = new Msdb()
   let sql = `update shipSRM_js set anpz='${anpz}' where id='${id}'`
   var rtdata = await db.update(sql)
   await saveczlog_util(sql, storage, '', '按钮权限分配', '修改', 'shipSRM_js')
   res_util(res, rtdata, true, "按钮权限分配成功")
})

//查询收藏
router.post('/queryScList', async function (req, res) {
   var db = new Msdb();

   var sql = `MERGE into  shipSRM_sccd  a USING 
	(SELECT c.id+a.id as id,a.id as userid,c.cdqx,d.cdly,d.cdmc,d.cdlx,c.update_time,
		c.sfsc from shipSRM_ryda a,shipSRM_js b,shipSRM_js_mx c,shipSRM_bgmenu d where a.jsid=b.id 
		and b.id=c.mainid and c.ssmk='PC' and d.jjcd='2' and d.cdbh=c.cdqx ) b on b.id=a.id
		when matched then UPDATE SET  cdmc=b.cdmc,cdly=b.cdly,cdqx=b.cdqx
		WHEN NOT MATCHED THEN  INSERT (id, mainid, sfsc,cdmc,cdqx,cdly,update_time,ssmk)  VALUES (b.id, b.userid,'否' ,b.cdmc,b.cdqx,b.cdly,b.update_time,'PC')
      WHEN NOT MATCHED BY SOURCE THEN  DELETE;`

   // 	MERGE into  qb_sccd  a USING 
   // (SELECT c.id+a.ygbh as id,a.ygbh ,c.cdqx,d.cdly,d.cdmc,d.cdlx,c.update_time,
   // 	c.sfsc from qb_ygda a,qb_ryjs b,qb_ryjs_qxmx c,qb_bgmenu d where a.ssjs=b.jsmc 
   // 	and b.id=c.mainid and c.ssmk='PC' and d.jjcd='2' and d.cdqx=c.cdqx ) b on b.id=a.id
   // 	when matched then UPDATE SET  cdmc=b.cdmc,cdly=b.cdly
   // 	WHEN NOT MATCHED THEN  INSERT (id, mainid, sfsc,cdmc,cdqx,cdly,cdlx,update_time,ssmk)  VALUES (b.id, b.ygbh,'否' ,b.cdmc,b.cdqx,b.cdly,b.cdlx,b.update_time,'PC')
   // 	WHEN NOT MATCHED BY SOURCE THEN  DELETE;
   var result = await db.query(sql);


   var userid = req.body.storage.userid;
   var querytensql = `select a.cdmc,a.sfsc,a.cdly,a.cdqx cdbh from shipSRM_sccd a
	 where a.mainid='${userid}' and a.sfsc='是'   ORDER BY a.update_time desc`
   var rtdata = await db.query(querytensql);
   res.send(JSON.stringify(rtdata.recordsets[0]));
});


//更新菜单是否收藏
router.post('/updateMenuList', async function (req, res) {
   var db = new Msdb();
   var userid = req.body.storage.userid;
   var cdqx = req.body.cdqx;
   var sfsc = req.body.sfsc;
   var cdmc = req.body.cdmc;

   var querytensql = `update  shipSRM_sccd  set sfsc='${sfsc}',update_time=getdate() where  cdqx='${cdqx}'  and  mainid='${userid}'`
   console.log(querytensql)
   var rtdata = await db.update(querytensql);
   res.send(JSON.stringify(rtdata));

});

//查询菜单
router.post('/queryMenuList', async function (req, res) {
   var db = new Msdb();
   var userid = req.body.storage.userid;
   var querytensql = `select a.sfsc,b.* from
(select a.jsbh,b.cdmc,b.cdqx,b.ssmk,b.sfsc
from shipSRM_js a,shipSRM_js_mx  b,shipSRM_ryda c 
 where a.id=b.mainid and c.jsid=a.id and c.id='${userid}' and ssmk='PC') a,
 (select * from  shipSRM_bgmenu   where ssmk='PC' and jjcd='1' ) b
 where a.cdqx=b.cdbh order by cdpx asc`
   var rtdata = await db.query(querytensql);

   var querytensql2 = `select b.sfsc,a.* from (select * from  shipSRM_bgmenu 
         where ssmk='PC' and jjcd='2' ) a left join shipSRM_sccd b on a.cdbh=b.cdqx
   where b.mainid='${userid}'  order by cdpx desc`
   var rtdata2 = await db.query(querytensql2);


   var result = {
      yjcdList: rtdata.recordsets[0],
      ejcdList: rtdata2.recordsets[0]
   }


   res.send(JSON.stringify(result));

});

//登录方法 - 已升级为预编译
router.post('/login', async function (req, res) {
   var db = new Msdb();
   const { username, password } = req.body.data;
   
   // 检查账号是否被锁定
   const lockKey = `login_lock_${username}`;
   const lockInfo = await Redisdb.get_async(lockKey);
   
   if (lockInfo) {
      const lockData = JSON.parse(lockInfo);
      const now = Date.now();
      
      // 检查锁定时间是否已过（30分钟）
      if (now < lockData.lockUntil) {
         const remainingMinutes = Math.ceil((lockData.lockUntil - now) / (1000 * 60));
         res_util(res, {}, false, `账号已被锁定，请${remainingMinutes}分钟后再试`);
         return;
      } else {
         // 锁定时间已过，清除锁定记录
         await Redisdb.del_async(lockKey);
      }
   }
   
   // 查询用户信息
   var result = await usequery_pre(`select * from shipSRM_ryda where zh=@zh and mm=@mm and ryzt !='B'`,{zh:username,mm:password});
   
   
   
   result.rowlength = result.length;
   
   if (result.rowlength == 0) {
      // 登录失败，增加错误计数
      const failKey = `login_fail_${username}`;
      const failInfo = await Redisdb.get_async(failKey);
      
      let failCount = 1;
      let firstFailTime = Date.now();
      
      if (failInfo) {
         const failData = JSON.parse(failInfo);
         const now = Date.now();
         
         // 检查是否在15分钟内（重置计数的时间窗口）
         if (now - failData.firstFailTime < 15 * 60 * 1000) {
            failCount = failData.count + 1;
            firstFailTime = failData.firstFailTime;
         }
      }
      
      // 更新失败记录
      await Redisdb.set_async(failKey, JSON.stringify({
         count: failCount,
         firstFailTime: firstFailTime
      }), 15 * 60); // 15分钟过期
      
      // 如果连续失败5次，锁定账号30分钟
      if (failCount >= 5) {
         const lockUntil = Date.now() + 30 * 60 * 1000; // 30分钟后解锁
         await Redisdb.set_async(lockKey, JSON.stringify({
            lockUntil: lockUntil,
            failCount: failCount
         }), 30 * 60); // 30分钟过期
         
         res_util(res, {}, false, "密码错误次数过多，账号已被锁定30分钟");
         return;
      }
      
      res_util(res, {}, false, `验证不通过，登录失败。连续错误${failCount}次，5次后将锁定账号`);
      return;
   }
   
   // 登录成功，清除失败记录
   const failKey = `login_fail_${username}`;
   await Redisdb.del_async(failKey);
   
   // 生成JWT令牌，4小时过期
   const userData = result[0];
   const jwtSecret = process.env.JWT_SECRET || 'hisconsrm_jwt_secret_key_2024';
   const token = jwt.sign({
      id: userData.id,
      username: userData.zh,
      name: userData.xm,
      role: userData.js,
      userType: userData.yhlx
   }, jwtSecret, { expiresIn: '4h' });
   
   // 将令牌存储到Redis，用于后续验证
   const tokenKey = `user_token_${userData.id}`;
   await Redisdb.set_async(tokenKey, token, 4 * 60 * 60); // 4小时过期
   
   // 返回用户信息和令牌
   result[0].username = "%@@#￥@@";
   result[0].password = "%@@#￥@@";
   result[0].token = token;
   
   res_util(res, result, true, "登录成功");
});

//验证JWT令牌的中间件
const verifyToken = async (req, res, next) => {
   const token = req.headers.authorization?.replace('Bearer ', '');
   
   if (!token) {
      return res_util(res, {}, false, "未提供访问令牌");
   }
   
   try {
      const jwtSecret = process.env.JWT_SECRET || 'hisconsrm_jwt_secret_key_2024';
      const decoded = jwt.verify(token, jwtSecret);
      
      // 检查Redis中的令牌是否仍然有效
      const tokenKey = `user_token_${decoded.id}`;
      const storedToken = await Redisdb.get_async(tokenKey);
      
      if (!storedToken || storedToken !== token) {
         return res_util(res, {}, false, "令牌已失效，请重新登录");
      }
      
      // 将用户信息添加到请求对象中
      req.user = decoded;
      next();
   } catch (error) {
      if (error.name === 'TokenExpiredError') {
         return res_util(res, {}, false, "登录已过期，请重新登录");
      }
      return res_util(res, {}, false, "无效的访问令牌");
   }
};

//登出方法
router.post('/logout', async function (req, res) {
   const token = req.headers.authorization?.replace('Bearer ', '');
   
   if (token) {
      try {
         const jwtSecret = process.env.JWT_SECRET || 'hisconsrm_jwt_secret_key_2024';
         const decoded = jwt.verify(token, jwtSecret);
         
         // 从Redis中删除令牌
         const tokenKey = `user_token_${decoded.id}`;
         await Redisdb.del_async(tokenKey);
      } catch (error) {
         // 令牌无效，忽略错误
      }
   }
   
   res_util(res, {}, true, "登出成功");
});

//刷新令牌方法
router.post('/refreshToken', async function (req, res) {
   const token = req.headers.authorization?.replace('Bearer ', '');
   
   if (!token) {
      return res_util(res, {}, false, "未提供访问令牌");
   }
   
   try {
      const jwtSecret = process.env.JWT_SECRET || 'hisconsrm_jwt_secret_key_2024';
      const decoded = jwt.verify(token, jwtSecret, { ignoreExpiration: true });
      
      // 检查Redis中的令牌是否仍然有效
      const tokenKey = `user_token_${decoded.id}`;
      const storedToken = await Redisdb.get_async(tokenKey);
      
      if (!storedToken || storedToken !== token) {
         return res_util(res, {}, false, "令牌已失效，请重新登录");
      }
      
      // 生成新的令牌
      const newToken = jwt.sign({
         id: decoded.id,
         username: decoded.username,
         name: decoded.name,
         role: decoded.role,
         userType: decoded.userType
      }, jwtSecret, { expiresIn: '4h' });
      
      // 更新Redis中的令牌
      await Redisdb.set_async(tokenKey, newToken, 4 * 60 * 60); // 4小时过期
      
      res_util(res, { token: newToken }, true, "令牌刷新成功");
   } catch (error) {
      return res_util(res, {}, false, "令牌刷新失败");
   }
});


//查询供应商档案是否有维护
router.post('/ifexist_gyswh', async function (req, res) {
   let {storage} = req.body;
   //console.log(JSON.stringify(req.body))
   var result = await usequery(`select * from shipSRM_gys where gysdm='${storage.ygbh}'`);
   res_util(res, result, true,"")
});

//查询注册申请是否有记录
router.post('/ifexist_shxydm',async function(req,res){
   
   var result = await checkbysql(`select * from shipSRM_gys where xysbdm='${req.body.shxydm}'`);
   
   res_util(res, result, result, result?"当前供应商已注册":"当前供应商未注册，申请提交成功")
})

//创建注册申请
router.post('/create_register', async function (req, res) {
   var db = new Msdb();
   let data = await usequery(`select top 1 * from shipSRM_zcb where dh='${req.body.phone}' order by create_time desc`);
   if (data.length > 0) {
      res_util(res, {}, false, "您已提交过注册申请，请耐心等待审核！")
   }
   else {
      let dqid = await getxl_util();
      var result = await db.update(`insert into shipSRM_zcb (id,dh,gysmc,xysbdm,lxr,clzt,create_time,create_by) values 
      ('${dqid}','${req.body.phone}','${req.body.gysmc}','${req.body.shxydm}','${req.body.lxr}','未处理',getdate(),'${req.body.gysmc}')`);

      res_util(res, result, true, "您已成功提交注册申请，请耐心等待审核！")
   }
})


//修改密码
router.post('/updatepassword',async function(req,res){
   var db = new Msdb();
   //console.log(JSON.stringify(req.body))

   var changepasswordsql = `update shipSRM_ryda  set mm='${req.body.data.password}' where zh='${req.body.data.username}'`
   var upmw = await db.update(changepasswordsql)

   console.log(changepasswordsql)
   var result = await db.query(`select * from shipSRM_ryda where zh='${req.body.data.username}' and mm='${req.body.data.password}' and  ryzt !='B'`);
   // console.log(result.recordsets[0][0]);
   //    console.log(JSON.stringify(result.recordsets[0].length));
   result.rowlength = result.recordsets[0].length;
   if (result.rowlength != 0) {
      result.recordsets[0][0].username = "%@@#￥@@"
      result.recordsets[0][0].password = "%@@#￥@@"
   }
   else;
   res.send(JSON.stringify(result));
});

//校验手机号 用户是否身份一致
router.post('/ifexist_phone',async function(req,res){
   var result = await checkbysql(`select * from shipSRM_ryda where dh='${req.body.phone}' and zh ='${req.body.username}'`);
   
   res_util(res, result, result, result?"您的手机号与系统记录一致":"您的手机号与系统记录不一致")
})


//修改密码
router.post('/update_Password',async function(req,res){
   var db = new Msdb();
   //console.log(JSON.stringify(req.body))

   var changepasswordsql = `update shipSRM_ryda  set mm='${req.body.newPassword}' where zh='${req.body.username}'`
   var upmw = await db.update(changepasswordsql)

   console.log(changepasswordsql)
   var result = await usequery(`select * from shipSRM_ryda where zh='${req.body.username}'  and  ryzt !='B'`);
   if(result.length==1){
      res_util(res, result, true, "修改密码成功")
   }
   else{
      res_util(res, {}, false, "修改密码失败")
   }
});

//查询菜单数据
router.post('/queryCdDataSource', async function (req, res) {
   //console.log(JSON.stringify(req.body))
   var rtdata = await usequery(`select * from shipSRM_bgmenu`);
   res_util(res, rtdata, true, "查询成功")
});




//查询数据字典列表
router.post('/dict_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_dict order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询数据字典列表通过id
router.post('/query_dict_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_dict where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_dict_mx where mainid ='${id}'`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})

//查询操作日志
router.post('/queryLogListAll', async function (req, res) {
   var db = new Msdb();
   const { ysyj, bh } = req.body;
   var sql = `select top 5000 * from  shipSRM_czlog  where 1=1`
   if (bh) {
      sql += ` and bh like '%${bh}%'`
   }
   if (ysyj) {
      sql += ` and ysyj like '%${ysyj}%'`
   }
   sql += ` order by czsj desc`
   var resdaata = await db.query(sql);
   res_util(res, resdaata.recordsets[0], true, "查询成功")
});



//查询数据字典列表通过dict bm
router.post('/query_dict_zdbm', async function (req, res) {
   var data = {}
   if(Array.isArray(req.body.zdbm)){
      for(let i=0;i<req.body.zdbm.length;i++){
         data[req.body.zdbm[i]]=await usequery(`select b.bm as value,b.mc as label from shipSRM_dict a,shipSRM_dict_mx b 
         where a.id=b.mainid and a.zdbm='${req.body.zdbm[i]}' order by sx desc`)
      }
   }
   else{
      data[req.body.zdbm]=await usequery(`select b.bm as value,b.mc as label from shipSRM_dict a,shipSRM_dict_mx b 
       where a.id=b.mainid and a.zdbm='${req.body.zdbm}' order by sx desc`)
   }
   res_util(res, { data }, true, "查询成功")
})




//查询数据字典列表通过dict bm
router.post('/query_olreport_bbbm', async function (req, res) {
   var data = {}
   if(Array.isArray(req.body.bbbm)){
      for(let i=0;i<req.body.bbbm.length;i++){
         let sqldata=await usequery(`select bbsql from shipSRM_olreport where bbbm='${req.body.bbbm[i]}'`)
         data[req.body.bbbm[i]]=await usequery(sqldata[0].bbsql)
      }
   }
   else{
   let sqldata=await usequery(`select bbsql from shipSRM_olreport where bbbm='${req.body.bbbm}'`)
   data[req.body.bbbm]=await usequery(sqldata[0].bbsql)
   }
   res_util(res, { data }, true, "查询成功")
})



//新增数据字典
router.post('/add_dict', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util()
   var insrtobj = await insert_table(formdata, "shipSRM_dict")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增数据字典', '新增', 'shipSRM_dict')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_dict_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增数据字典明细', '新增', 'shipSRM_dict_mx')
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_dict_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "新增数据字典成功")
})


//更新数据字典
router.post('/update_dict', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_dict")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改数据字典', '修改', 'shipSRM_dict')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_dict_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_dict_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_dict_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_dict_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_dict_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新数据字典成功")
})




//  删除数据字典
router.post('/delete_dict', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_dict where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_dict_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除数据字典', '删除', 'shipSRM_dict')
   res_util(res, {}, true, "删除成功")
})




//查询在线表单列表
router.post('/olreport_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_olreport order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询在线表单列表通过id
router.post('/query_olreport_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_olreport where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_olreport_mx where mainid ='${id}'`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})


//查询在线表单列表通过olreport bm
router.post('/query_olreport_zdbm', async function (req, res) {
   var data = {}
   if(Array.isArray(req.body.zdbm)){
      for(let i=0;i<req.body.zdbm.length;i++){
         data[req.body.zdbm[i]]=await usequery(`select b.bm as value,b.mc as label from shipSRM_olreport a,shipSRM_olreport_mx b 
         where a.id=b.mainid and a.zdbm='${req.body.zdbm[i]}' order by sx desc`)
      }
   }
   else{
      data[req.body.zdbm]=await usequery(`select b.bm as value,b.mc as label from shipSRM_olreport a,shipSRM_olreport_mx b 
       where a.id=b.mainid and a.zdbm='${req.body.zdbm}' order by sx desc`)
   }
   res_util(res, { data }, true, "查询成功")
})



//新增在线表单
router.post('/add_olreport', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util()
   var insrtobj = await insert_table(formdata, "shipSRM_olreport")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增在线表单', '新增', 'shipSRM_olreport')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_olreport_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增在线表单明细', '新增', 'shipSRM_olreport_mx')
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_olreport_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "新增在线表单成功")
})


//更新在线表单
router.post('/update_olreport', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_olreport")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改在线表单', '修改', 'shipSRM_olreport')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_olreport_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_olreport_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_olreport_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_olreport_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_olreport_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新在线表单成功")
})




//  删除在线表单
router.post('/delete_olreport', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_olreport where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_olreport_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除在线表单', '删除', 'shipSRM_olreport')
   res_util(res, {}, true, "删除成功")
})


//查询在线序列列表
router.post('/olseqlb', async function (req, res) {
   var rtdata = await usequery(`select a.*,b.current_value from shipSRM_olseq a LEFT JOIN sys.sequences b on a.xlbh=b.name order by a.create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询在线序列列表通过id
router.post('/query_olseq_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_olseq where id='${req.body.id}' order by create_time desc`)
   res_util(res, { formdata: data[0] }, true, "查询成功")
})

//新增在线序列
router.post('/add_olseq', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbh = await checkbysql(` select top 1 * from shipSRM_olseq where xlbh='${formdata.xlbh.value}'`)
   if (ifbh) {
      res_util(res, { formdata }, false, "新增失败，在线序列已存在")
      return
   }
   //不存在编码插入
   var insrtobj = await insert_table(formdata, "shipSRM_olseq")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增在线序列', '新增', 'shipSRM_olseq')
   res_util(res, { formdata: insrtobj.data }, true, "新增成功")
})

//更新在线序列
router.post('/update_olseq', async function (req, res) {
   let { formdata, storage } = req.body
   var ifbm = await checkbysql(` select top 1 * from shipSRM_olseq where 
   xlbh='${formdata.xlbh.value}' and id !='${formdata.id.value}'`)
   if (ifbm) {
      res_util(res, { formdata }, false, "更新失败，物料编码已存在")
      return
   }
   //不存在更新
   let updatobj = await update_table_byid(formdata, "shipSRM_olseq")
   await saveczlog_util(updatobj.updatesql, storage, '', '更新在线序列', '更新', 'shipSRM_olseq')
   res_util(res, { formdata: updatobj.data }, true, "更新成功")
})

//删除在线序列
router.post('/delete_olseq', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_olseq where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除在线序列', '删除', 'shipSRM_olseq')
   res_util(res, {}, true, "删除成功")
})

//生成序列
router.post('/syn_olseq', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
try {
   for(var i=0;i<=selectedRows.length-1;i++){
      var xlobj=selectedRows[i]
      await db.update(`
      CREATE SEQUENCE ${xlobj.xlbh}
    AS INT
    START WITH ${xlobj.csz}
    INCREMENT BY ${xlobj.jg}
    MINVALUE ${xlobj.zxz}
    MAXVALUE ${xlobj.zdz}
    ;
`)

await db.update(`
    update shipSRM_olseq set zt='已同步' where id ='${xlobj.id}'
`)
   }
} catch (error) {
   
}
   

   res_util(res, {}, true, "新增成功")
})

//使序列开始值为初始值
router.post('/reset_olseq', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
try {
   for(var i=0;i<=selectedRows.length-1;i++){
      var xlobj=selectedRows[i]
      await db.update(`
      ALTER SEQUENCE ${xlobj.xlbh}
      RESTART WITH   ${xlobj.csz};`)
   }
} catch (error) {
   
}
   

   res_util(res, {}, true, "新增成功")
})


module.exports = router; 